CREATE TABLE `SRTrade`.`MsgSRParentExecution` (
`fillNumber` BIGINT NOT NULL DEFAULT 0 COMMENT 'SpiderRock execution number (globally unique over trailing 10 days)',
`sysRealm` ENUM('None','SysTest','NMS','CME','FR2','LD4','DR') NOT NULL DEFAULT 'None',
`sysEnvironment` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
`runStatus` ENUM('None','Prod','Beta','UAT','SysTest') NOT NULL DEFAULT 'None',
`version` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'record version number;starts at zero and goes up every time an execution record is re-published (eg, bust, +1m marks, +10m marks, etc)',
`execStatus` ENUM('None','Fill','Bust','Correct','Reject','SysRej') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock execution status (Fill,Bust,Correct,Reject,SysRej)',
`execShape` ENUM('None','Single','MLegTop','MLegLeg','SpreadTop','SpreadLeg','SingleLeg') NOT NULL DEFAULT 'None',
`packageId` BIGINT NOT NULL DEFAULT 0 COMMENT 'SR package Id',
`parentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SR parent number',
`parentVersion` SMALLINT NOT NULL DEFAULT 0 COMMENT 'SPDR order instance number (1 = original order; 2 = 1st cancel/replace, etc)',
`baseParentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SPDR order number (initial number in cancel/replace sequence) (also, source parent for dynamically generated orders;eg auto-hedges)',
`clOrdId` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SR child order clOrdID resulting in fill',
`reviewClOrdId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'review session clOrdId (usually from inbound review FIX session)S',
`altFillId` BIGINT NOT NULL DEFAULT 0 COMMENT 'alternate fill ID (usually from AwayTktGateway.pkey.clientFillID)',
`altOrderId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate order ID (usually clOrdId from client)',
`altCrossId` VARCHAR(24) NOT NULL DEFAULT '',
`altLegRefId` VARCHAR(24) NOT NULL DEFAULT '',
`altAccnt` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) "long" account string (optional) [used to map between client and SR account strings]',
`altUserName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) user name (optional) [used to map between client and SR account strings]',
`srcRoutingCode` TINYTEXT NOT NULL DEFAULT '' COMMENT 'inbound FIX routing code, if any',
`riskGroupId` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'riskGroupId (parent order group ID) for this execution report',
`triggerGroupId` BIGINT NOT NULL DEFAULT 0 COMMENT 'WaitTrigger group Id',
`triggerTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'WaitTrigger trigger timestamp (nanoseconds since epoch)',
`streetClOrdId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'street side clOrdId (clOrdId or equivalent of the street side order) [SR/street gateway generated]',
`streetOrderId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'street side orderId (orderId or equivalent of the street side order) [from street side execReport]',
`parentShape` ENUM('None','Single','Cross','MLeg','MLegCross') NOT NULL DEFAULT 'None' COMMENT 'shape of originating parent order',
`secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'execution security key',
`secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'execution security key',
`secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'execution security key',
`secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'execution security key',
`secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'execution security key',
`secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'execution security key',
`secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'execution security key',
`secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'execution security key',
`secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'execution security type [Stock, Future, Option]',
`ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'base stock key (used for symbol risk aggregation)',
`ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'base stock key (used for symbol risk aggregation)',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'base stock key (used for symbol risk aggregation)',
`accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SpiderRock trading accnt [broker pkey]',
`clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR client firm',
`spdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock parent order source code [broker pkey]',
`groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SpiderRock parent broker number [broker pkey]',
`coreClientFirm` VARCHAR(16) NOT NULL DEFAULT '',
`sponsorClientFirm` VARCHAR(16) NOT NULL DEFAULT '',
`routingCode` TINYTEXT NOT NULL DEFAULT '' COMMENT 'SpiderRock market routing code',
`engineName` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'SpiderRock execution engine that handled the parent order',
`gatewayName` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'StreetGateway server than handled the child order (if any)',
`execRole` ENUM('None','DirectAccnt','AwayGiveup','RiskDrop','AwayDrop','PullDrop') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock relationship to this execution record',
`execBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR Assigned executing broker code',
`execBrkrMPID` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'FINRA/Assigned exec broker MPID (if any)',
`altBrkrMPID` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'alternative broker MPID for a given route (if needed)',
`execBrkrAccnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'account at executing broker (if any)',
`execBrkrClFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'client/firm at executing broker (if any)',
`execBrkrUserName` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'userName at execution broker (if any)',
`clearingFlipType` ENUM('None','CMTA','Giveup','QSR','NSCC_Flip','DVP','BrkrAccnt') NOT NULL DEFAULT 'None' COMMENT 'Clearing Flip Type (None, ExecBroker, CMTA, Giveup, DTCC, QSR, etc)',
`clearingFlipFirm` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'Clearing Flip Firm (if any)',
`clearingFlipAccnt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'Clearing Flip Account (if any)',
`clearingFirm` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'clearing firm',
`clearingAccnt` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'clearing firm account (if any)',
`origExecID` TINYTEXT NOT NULL DEFAULT '' COMMENT 'original execution ID string (child order)',
`lastExecID` TINYTEXT NOT NULL DEFAULT '' COMMENT 'most recent execution ID (same as origExecID unless CANCEL/CORRECTION has been processed)',
`remoteText` TINYTEXT NOT NULL DEFAULT '' COMMENT 'text comment from remote endpoint (if any)',
`orderSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None' COMMENT 'order side',
`spdrOrderStatus` ENUM('PendNew','New','PendClose','Closed','Rejected','SendReject') NOT NULL DEFAULT 'PendNew',
`spdrCloseReason` ENUM('None','Cancelled','Filled','Replaced','Expired','Limit','System','LegReject','DoneForDay','IOCExpire','UserCxl','NoProgress','TooManyRej','ReplReject','AlgoClose','Restart','InvldParentLimit','FilledRepl','ForceClose','DmaReject','DmaExpire','DmaBrkrCxl','ReviewReject','MarketState','AlgoReject','ReviewTimeout','ChildReject','ChildCancel','ReviewClose','UPrcRange','LegBrkrClosed','ExchRisk','CrossFailed') NOT NULL DEFAULT 'None',
`cumFillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'cumulative fills (this parent number only)',
`avgFillPrice` DOUBLE NOT NULL DEFAULT 0,
`cumLegFillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'cumulative fills (spread only)',
`avgLegFillPrice` DOUBLE NOT NULL DEFAULT 0,
`leavesQty` INT NOT NULL DEFAULT 0,
`priceType` ENUM('None','Explicit','Offset','Zero') NOT NULL DEFAULT 'None',
`childShape` ENUM('None','Single','Cross','MLeg','MLegCross') NOT NULL DEFAULT 'None',
`childSize` INT NOT NULL DEFAULT 0 COMMENT 'child order size',
`childPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'child order price',
`childDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'child order generation date/time',
`childExch` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'child order exchange',
`childExDest` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'child order exchange destination code',
`childExecBroker` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'executing broker that child order was routed to',
`childIsDirected` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'child order isDirected flag',
`childIsoSweep` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'child order ISO sweep flag',
`childOrderHandling` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'child order handling string from the algo that generated the child order responsible for this fill',
`childAlgoHandler` ENUM('None','ActiveTaker','ActiveMaker','Auction','Responder','Matrix','Cross','Face','Extern','MLegHandler','AutoHedge','Sprayer','Legger','Restart','Orphan','UDefSpread','RFQRequest','MLegResponder','LeggerX','ExchPing','BrkrReview','AuctionResponder','TakeSweep','TestChild') NOT NULL DEFAULT 'None' COMMENT 'algo handler for this child order',
`childCreateReason` ENUM('None','CheckAddExch','DarkCross','DmaExtern','DmaSmart','MakeJoin','MakeNbboImpr','MakePostLimit','MakeSelfImpr','MarketAuction','MatrixImpr','MatrixJoin','MatrixLvlSweep','MatrixMaxSweep','MLegSeeker','OptAuctionResp','OptFaceHandler','RelWaitTrigger','SprdDma','SprdFace','SprdSeeker','TakeBrkr','TakeExch','TakeNbbo','RFQRequest','MLegResponse','BrkrCross','PostFlash','MakeOff','MakeFlashImprv','AwayExtern','MatrixCross','ExchPing','BrkrReview','AuctionResponse','SweepTake','TestChild') NOT NULL DEFAULT 'None',
`childCancelReason` ENUM('None','MakeException','MakeMaxRiskSize','MakeLimitError','MakeCxlImpr','MakeLmtPrc','MakeSelfImpr','MakeJoinImprv','MakeReJoin','MakeBhnd','MakeAlone','MakeAloneF','MakeOffMkt','MakeMaxExpose','MakeJoinImpr','ContLmtPrc','ChildCxlAll','ChildFlashCxl','ChildIOCTimeout','ForceRetry','StkCxlAll','FutCxlAll','OptCxlAll','FastCxlUBid','FastCxlUAsk','FastCxlUMin','FastCxlUMax','MktMiss','CxlClear','CxlRplDMA','CxlRplTurn','CxlRplSize','CxlRplPrc','CxlRplMake','CxlRplExch','MLegSeekSwitch','MLegSeekChange','MLegSeekND','MLegSeekLimit','MLegDMA','MLegSprdLimit','ParentRplRej','ParentReject','ParentClose','LeggerSwitch','AccelTake','EnginePreOpen','LegLmtPrcRng','MakeLmtPrcErr','ProgTerminate','TickSzErr','ContBrkrTerm','ContCancelHold','ContLimitErr','ContMaxUPrc','ContMinUPrc','ContMktData','ContMktHalt','ContRiskHold','ContSecType','ContStkData','ContStkState','ContFutData','ContFutState','ContOptData','ContOptState','ContUFutData','ContUFutState','ContUMktData','ContUMktState','LegExposeSize','LegLimitErr','LegMarketPrc','MLegCobLmtPrc','MLegLeggerChange','MLegLeggerSwitch','MLegSprdLmtPrc','SprdMktPrc','ContUMktPrc','ContUMktQte','SGContCxl','SGSysCxl','CxlRplPart','CxlRplAlgo','ContingentCancel','SwitchMOC','CxlRplReview','CxlRplAltR','SurfPrcErr','UserHold','DayClose','PendNewTimeout','ActiveHold') NOT NULL DEFAULT 'None',
`childMakerTaker` ENUM('None','Maker','Taker') NOT NULL DEFAULT 'None',
`childUBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier market bid at @ child order send time',
`childUAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier market ask at @ child order send time',
`childBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market bid at the @ child order send time',
`childAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market ask at the @ child order send time',
`childMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'mid-market (SR surface price if option) @ child order send time',
`childFairWidth` DOUBLE NOT NULL DEFAULT 0 COMMENT 'model fair width @ child order create',
`childVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'child order volatility @ childLimitRefUPrc',
`childProb` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR probability for the child order @ child order send time',
`childLimitRefUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'limit reference underlier price @ child order send time',
`childLimitPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'parent order limit price @ child order send time',
`childVolAtm` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility @ child order send time',
`childPosType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None' COMMENT 'child order position type',
`childFirmOptPosition` INT NOT NULL DEFAULT 0 COMMENT 'effective firm option position @ child order send time',
`childSSaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'short sale flag',
`childFirmStkPosition` INT NOT NULL DEFAULT 0 COMMENT 'effective firm stock position @ child order send time',
`childFirmOpenOrders` INT NOT NULL DEFAULT 0 COMMENT 'open sell orders counted in marking @ child order send time',
`childLocateQuan` INT NOT NULL DEFAULT 0 COMMENT 'available locate quantity (if selling short) @ child order send time',
`childLocateFirm` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'firm granting the locate',
`childLocatePool` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'locate pool @ firm granting locate',
`childMktStance` ENUM('None','ExchMrkt','NbboMrkt','ExchImpr','NbboImpr','ExchJoin','NbboJoin','Away') NOT NULL DEFAULT 'None' COMMENT 'child order was expected to be marketable @ child send time',
`childCxlAttempted` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'cancel attempt was made on the child order prior to receiving this fill',
`childCxlFillLatency` FLOAT NOT NULL DEFAULT 0 COMMENT 'cancel attempt -to- fill report latency (in ms)',
`childMethod` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'execution method string',
`childSource` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'execution source string',
`firmType` ENUM('None','Customer','Firm','MarketMaker','ProCustomer','BrokerDealer','AwayMM','FirmJBO','BrkrDlrCust') NOT NULL DEFAULT 'None' COMMENT 'child order firm type [Customer, ProCust, Firm, MM, etc]',
`priAggGroup` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'primary aggregation group',
`secAggGroup` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'secondary aggregation group',
`directedClientFirm` VARCHAR(16) NOT NULL DEFAULT '',
`isCommPaying` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
`fillTransactDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'transaction date/time as reported by exchange or down stream broker',
`fillTs` DOUBLE NOT NULL DEFAULT 0 COMMENT 'fill report ts (seconds since startup; 100 nanosec resolution)',
`fillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'Date/time of fill arrival (SRDateTime)',
`fillExch` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'ExDest code from child order execution report',
`fillExecId` TINYTEXT NOT NULL DEFAULT '' COMMENT 'street side execution Id',
`fillExecRefId` TINYTEXT NOT NULL DEFAULT '' COMMENT 'street side execution ref Id (only used when busting an execution)',
`fillLegRefId` BIGINT NOT NULL DEFAULT 0 COMMENT 'legRefId for multileg fills',
`fillLegRatio` INT NOT NULL DEFAULT 0 COMMENT 'legRatio (if spread order)',
`fillExchFee` FLOAT NOT NULL DEFAULT 0 COMMENT 'SpiderRock estimate of the exchange fee based on liquidity tags (best effort)',
`fillMarket` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'usually from execReport.lastMkt as reported by child order venue',
`fillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'fill price',
`fillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'fill quantity',
`fillReportDetail` TINYTEXT NOT NULL DEFAULT '' COMMENT 'extra detail (if any) from child execution',
`fillBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market bid @ fill arrival',
`fillAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market ask @ fill arrival',
`fillMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'mid-market (or SR surface price if option) @ fill arrival',
`fillFairWidth` DOUBLE NOT NULL DEFAULT 0 COMMENT 'model fair width @ fill time',
`fillUMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier mark @ fill arrival',
`fillUBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier market bid @ fill arrival',
`fillUAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier market bid @ fill arrival',
`fillUBidSz` INT NOT NULL DEFAULT 0 COMMENT 'underlier bid size @ fill arrival',
`fillUAskSz` INT NOT NULL DEFAULT 0 COMMENT 'underlier ask size @ fill arrival',
`fillVolAtm` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility @ fill arrival',
`fillMark1M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'mid-market (or SR surface price if option) @ fill arrival + 1m',
`fillMark10M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'mid-market (or SR surface price if option) @ fill arrival + 10m',
`fillBid1M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market bid @ fill arrival + 1m',
`fillAsk1M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market ask @ fill arrival + 1m',
`fillBid10M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market bid @ fill arrival + 10m',
`fillAsk10M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market ask @ fill arrival + 10m',
`fillUMark1M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier mark @ fill arrival + 1m',
`fillUMark10M` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier mark @ fill arrival + 10m',
`fillVolAtm1M` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility (options only) + 1m',
`fillVolAtm10M` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility (options only) + 10m',
`fillState1M` ENUM('None','OK','Error') NOT NULL DEFAULT 'None',
`fillState10M` ENUM('None','OK','Error') NOT NULL DEFAULT 'None',
`fillVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill volatility @ fillLimitRefUPrc & fillLimitRefSDiv',
`fillVolS` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill volatility @ surfaceUPrc & surfaceSDiv',
`fillProb` FLOAT NOT NULL DEFAULT 0 COMMENT 'T+10m probability for the fill @ fill arrival send time',
`fillLimitRefUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'limit reference underlier price @ fill arrival time',
`fillLimitRefSDiv` DOUBLE NOT NULL DEFAULT 0 COMMENT 'limit reference sdiv value @ fill arrival time',
`fillLimitPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'parent order limit price @ fill arrival time',
`fillVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill vega',
`fillGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill gamma',
`fillDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill delta',
`fillTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill theta',
`fillVeRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill ve / fill atm ve',
`fillBeta` FLOAT NOT NULL DEFAULT 0 COMMENT 'SpiderRock estimate of beta to SPX',
`riskVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'risk vega',
`riskWtVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'risk gamma',
`riskNValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'risk delta',
`riskDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'risk delta',
`riskDDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'risk ddelta',
`riskRm1` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`riskRm2` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`riskRm3` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`riskRm4` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`riskRm5` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`riskRm6` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`riskRm7` FLOAT NOT NULL DEFAULT 0 COMMENT 'user defined (from parent order) [used to manage order groups]',
`marginUDnVDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier down, vol down',
`marginUDnVUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier down, vol up',
`marginUUpVDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up, vol down',
`marginUUpVUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up, vol up',
`riskU15Dn` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier dn 15% shock slide',
`riskU15Up` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up 15% shock slide',
`riskU50Dn` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier dn 50% shock slide',
`riskU50Up` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up 50% shock slide',
`fillBrkrRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'billing brokerage rate (tier 1)',
`fillRoutingRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'billing routing rate (tier 1)',
`mmPrefCode` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'market maker preference code (if any)',
`mmCredit` FLOAT NOT NULL DEFAULT 0 COMMENT 'expected market maker credit (if any)',
`riskCode` ENUM('None','R1','R2','R3','R4','R5','R6','R7','R8') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock Risk Code (if any)',
`billingSecType` ENUM('None','Equity','Future','EqtOption','FutOption') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock billing security type',
`billingCategory` ENUM('None','Alpha','AlphaTop50','TiedMaker','TiedTaker','SOR','DMA','AuctionResponse','Facilitate','AlphaFacilitate','AwayTrade','SymOverride','Extern','NonBillable','Seeker','Legger','Drop','AwayAlgo','IsoSweep','SpdrSweep','GTH','AlphaGTH','BlockAuction','BlockResponse') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock billing category',
`spdrLiquidityTag` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'SpiderRock normalized liquidity tag',
`exchLiquidityTag` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'liquidity tag as reported by downstream venue (if any)',
`fillExchDetail` TINYTEXT NOT NULL DEFAULT '' COMMENT 'other fix tags (tag:value#tag:value) [additional detail used for analysis]',
`lastCapacity` ENUM('None','Agency','Principal','Individual','Proprietary','AgentOtherMember','RisklessPrincipal') NOT NULL DEFAULT 'None' COMMENT 'child order capacity',
`exchFirmType` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'used to reflect field from a downstread execution back up to upstream fill report',
`extExecBroker` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'used to reflect field from a downstream destination back up to and upstream fill report',
`relationshipType` ENUM('None','EXSMember','EXSRouter','EXSTech','Platform','SRConnect','Advisor') NOT NULL DEFAULT 'None' COMMENT 'type of SR <-> client relationship',
`clArriveMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'client specified arrival mark (passed through from parent order)',
`parentDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'parent order creation date/time',
`parentOrderSize` INT NOT NULL DEFAULT 0 COMMENT 'parent order size',
`parentUBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier market bid @ parent order arrival',
`parentUAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier market ask @ parent order arrival',
`parentUMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier mid mark @ parent order arrival',
`parentBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market bid @ parent order arrival',
`parentAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'market ask @ parent order arrival',
`parentMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'mid-market (or SR surface price) @ parent order arrival',
`parentFairWidth` DOUBLE NOT NULL DEFAULT 0 COMMENT 'model fair width @ parent order arrival',
`parentSurfVol` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SR surf vol @ parent order arrival',
`parentLimitVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'parent order limit volatilty (if any)',
`parentLimitPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'parent order limit price @ parent order arrival',
`parentLimitRefUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'limit reference underlier price @ parent order arrival',
`parentTheoVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'client supplied theoretical volatility (used for markup only) [copied from parent order]',
`parentTheoPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'option price corresponding parentTheoVol',
`parentPosType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None' COMMENT 'parent order position type',
`parentSSaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'short sale flag on parent order',
`noticeNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'RFR / Auction noticeNumber (if any)',
`numMakeExchanges` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of exchanges (1 - 4) on which to publish public making orders. Effective number might be less than requested number if sufficient exchanges are not available.',
`publicSize` ENUM('None','Randomize','MktSize','FullSize','MktSizeA','MktSizeB','MktSizeC','FullSizeR','Max25Pct','Max50Pct','Max75Pct','NoSize') NOT NULL DEFAULT 'None' COMMENT 'public order size handling: None=use default size handling (usually limits public size to ''typical'' market size); Randomize=randomize public size; FullSize=expose entire order size where possible',
`canOverlapCxlRepl` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'can execution engines overlap cancel/replace operations [order can overfill if YES] (at most one active overlapping cxl/replace operation for each parent order)',
`progressRule` ENUM('None','Twap','Vwap','TwapReset','VwapReset','FastReset','SlowReset','TwapAlpha','VwapAlpha','TwapAlphaC','VwapAlphaC','AutoComplete','AllowImmediate','Manual','SpdrPulse','IOC') NOT NULL DEFAULT 'None' COMMENT 'Immediate = all size immediately available;TWAP = size released in time intervals;VWAP = size released in volume intervals;',
`exchMaskEnabled` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'exchMask != 0',
`timeInForce` ENUM('None','Day','IOC','GTD','ExtDay','Week','ExtWeek') NOT NULL DEFAULT 'None' COMMENT 'Parent order market session',
`parentOrderHandling` ENUM('None','ActiveTaker','PostOnly','DMA','MktOnOpn','MktOnCls','Facilitate','Matrix','Legger','Seeker','SeekerLegger','CrossResponse','AuctionResponse','MLegAuctionResp','RFQRequest','AwayAlgo','ExchPing','BlockAuction','BlockResponse','SweepTake','CobMaker','FaceOmni','TestParent') NOT NULL DEFAULT 'None' COMMENT 'Primary/Take Algo Handler',
`parentBalanceHandling` ENUM('None','PostWith','PostTurn','PostImprove','PostLimit','MaxIntern','PostWthF','PostImprvR','PostFlash','PostFlashW','PostPeg','PostFlashI') NOT NULL DEFAULT 'None' COMMENT 'Make Algo Handler',
`orderLimitType` ENUM('None','Market','MarketArrival','Prc','PrcDe','PrcDeX','PrcDeT','PrcDeP','PrcDeXT','PrcDeXP','Vol','VolX','PrcV','PrcVX','NoLimit','RelMid','RelJoin','RelCross','SmrtFast','SmrtNorm','RelTurn','PrcDeEm','VolEm','Aux','UPrcPct','PkgNeutral','RcPrem','SynthLimitMM','SynthLimitBW','SynthLimitWW') NOT NULL DEFAULT 'None' COMMENT 'Primary Limit Type (Prc, Vol, Rel, Market, Smrt, etc)',
`orderLimitClass` ENUM('Simple','Surface','Probability','SurfProb') NOT NULL DEFAULT 'Simple' COMMENT 'Simple = LimitPrice, Surface = BEST(LimitPrice, SurfLimit), Probability = BEST(LimitPrice, ProbLimit)',
`takeReachRule` ENUM('None','Delayed','Passive','WeakOnly','RespondOnly','FullSize','ISOSweep','AllOrNone','QtyOrMore','UpToQty','AtMost25','AtMost50','MinTakeFee') NOT NULL DEFAULT 'None' COMMENT 'Immediate = reach room immediately available; Delayed = available after [1-3] seconds; Passive = available if contra side aggresses; WeakOnly = only take if available size < avgMarketSize; ISOSweep = Intermarket Sweep [requires WaitTrigger]',
`overrideCode` ENUM('None','SDivOnly','DDivOnly','Both') NOT NULL DEFAULT 'None' COMMENT 'SDiv/DDiv override rule (applies if LimitType = Vol)',
`alphaType` ENUM('None','Static','Eagle','Hawk','Falcon','Relative') NOT NULL DEFAULT 'None' COMMENT 'Affects limit probabilities (applies if LimitClass = Probability)',
`parentStrategy` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'client strategy [usually client supplied]',
`userName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user name associated with the parent order',
`autoHedge` ENUM('None','Static','AutoMid','AutoCrx','AutoTrn','SpdrAuto','Spdr10S','Spdr30S','Spdr90S','Spdr5M','Spdr30M','SpdrDay','SmartFast','SmartNorm','FastCrx','FastDark','SlowDark','AlphaVwap1pct','AlphaVwap2pct','AlphaVwap5pct','AlphaVwap25pct','Custom','AwayAlgo') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge algorithm (if any)',
`hedgeSecKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge sec key (if any)',
`hedgeSecKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge sec key (if any)',
`hedgeSecKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'auto-hedge sec key (if any)',
`hedgeSecKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'auto-hedge sec key (if any)',
`hedgeSecKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'auto-hedge sec key (if any)',
`hedgeSecKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'auto-hedge sec key (if any)',
`hedgeSecType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'auto-hedge sec type (if any)',
`hedgeBetaRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'portion of executed $delta to auto-hedge (can be 1.0 / Beta for beta hedging) [-4.0 to +4.0])',
`hedgeScope` ENUM('None','Accnt','RiskGroup') NOT NULL DEFAULT 'None' COMMENT 'hedge group scope',
`userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to parent and child executions and reports as well as FIX drops',
`userData2` TINYTEXT NOT NULL DEFAULT '' COMMENT 'client supplied data field; passes through to parent and child executions and reports as well as FIX drops',
`years` FLOAT NOT NULL DEFAULT 0 COMMENT 'years to expiration',
`underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'option delivery underliers per contract',
`underlierType` ENUM('None','Equity','Other','FX') NOT NULL DEFAULT 'None' COMMENT 'type of underlier (affects $greek calculations)',
`tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single tick change in display premium (pointValue = tickValue / tickSize)',
`pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (pointValue = tickValue / tickSize)',
`pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
`uPrcRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'UPrcRatio (SymbolRatio) from product definition',
`minTickSize` FLOAT NOT NULL DEFAULT 0 COMMENT 'minimum market price variation (dnTickSize if on a boundary)',
`priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock price display format code',
`uPriceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock underlier price display format code',
`responseType` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'usually an exch auction response type',
`nbboBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'nbbo bid @ child order send',
`nbboAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'nbbo ask @ child order send',
`childUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlier price when child order sent',
`exchBidSz` INT NOT NULL DEFAULT 0 COMMENT 'exch bid size @ child order send',
`exchAskSz` INT NOT NULL DEFAULT 0 COMMENT 'exch ask size @ child order send',
`liveUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SpiderRock internal use only',
`liveMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SpiderRock internal use only',
`srcTimestamp` BIGINT NOT NULL DEFAULT 0,
`sgwTimestamp` BIGINT NOT NULL DEFAULT 0,
`engTimestamp` BIGINT NOT NULL DEFAULT 0,
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of this record [not necessarily the timstamp of the fill itself]',
CONSTRAINT nonnegative_parentNumber CHECK(ASCII(parentNumber) < 56),
CONSTRAINT nonnegative_baseParentNumber CHECK(ASCII(baseParentNumber) < 56),
CONSTRAINT nonnegative_clOrdId CHECK(ASCII(clOrdId) < 56),
CONSTRAINT nonnegative_riskGroupId CHECK(ASCII(riskGroupId) < 56),
CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
CONSTRAINT nonnegative_noticeNumber CHECK(ASCII(noticeNumber) < 56),
PRIMARY KEY USING HASH (`fillNumber`),
KEY `AccntIndex` (`accnt`) USING HASH,
KEY `ClientFirmIndex` (`clientFirm`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpdrParentExecution records are published every time a parent order execution is received. They also update as additional post execution mark information is available at F+1m and F+10m.';